/// File: DbConnections.cs 
/// Date: 03/15/2012
/// Author: Phil Allison (stoneguard@gmail.com)
/// Purpose: These are general support functions wrapped within a class in the base namespace for this library
/// 
/// copyright (c) 2001-2012, Phil Allison. Under the MIT License http://www.opensource.org/licenses/mit-license.php  
/// Feel free to reuse and modify this code as long as credit is given to the original author.
/// Modified code must be noted in this section.
using System;
using System.Xml;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Win32;
using System.Collections.Generic;
using System.Collections;

namespace sgsBase
{
	/// <summary>
	/// DbConnections - Class for managment of database connections
	/// </summary>
	[Serializable] public class DbConnections
	{
		DbConnector _connector;
		protected string _database = "";

		private static string _DBPREFIX = ""; // should be set to "conn-" for production, "dev-" for development
		public static string DBPrefix
		{
			get
			{
				if (_DBPREFIX == "")
				{
					_DBPREFIX = ApplicationHelper.CfgGetAppSetting("DB_Environment");
					if (_DBPREFIX == null || _DBPREFIX == "")
						_DBPREFIX = "conn-";
				}
				return _DBPREFIX;
			}
		}

		public DbConnections(string Database = "")
		{
			if (Database != "")
				_database = Database;
			LoadConnector();
		}

		#region Properties
		public string DataBase
		{
			get { return _database; }
			set 
			{ 
				_database = value;
				LoadConnector(); //force to proper connection string
			}
		}

		#endregion

		#region Transaction Level functions
		public void BeginTransaction(IsolationLevel IL)
		{
			Open();

			if (_connector.Connection.State != ConnectionState.Open)
			{
				_connector.Connection.Open();
			}
			_connector.Transaction = _connector.Connection.BeginTransaction(IL);
		}

		public void BeginTransaction()
		{
			try
			{
				BeginTransaction(IsolationLevel.ReadCommitted);
			}
			catch(Exception er)
			{
				throw new Exception("Connection Error", er);
			}
		}

		public void CommitTransaction()
		{
			if (_connector.Transaction == null)
				throw new Exception("Transaction is not begining");
			try
			{
				_connector.Transaction.Commit();
				_connector.Transaction = null;
			}
			catch(Exception er)
			{
				throw new Exception("Connection Error", er);
			}
		}

		public void RollbackTransaction()
		{
			if (_connector.Transaction == null)
				throw new Exception("Transaction is not begining");
			try
			{
				_connector.Transaction.Rollback();
				_connector.Transaction = null;
			}
			catch(Exception er)
			{
				throw new Exception("Connection Error", er);
			}
		}
		#endregion

		/// <summary>
		/// Open connection to SQL database
		/// </summary>
		/// This function works to maintain one open connection to the database so 
		/// that multiple connections are not utilized and wasted by multiple calls
		/// to the database in one transaction set.
		public void Open()
		{
			try
			{
				if (_connector.Connection.State != ConnectionState.Open) 
				{
					_connector.Connection.Open();
				}
			}
			catch(Exception)
			{
				try
				{
					LoadConnector();
					_connector.Connection.Open();
				}
				catch(Exception er)
				{
					
					throw new Exception("Connection Error",er);
				}
			}
		}
		
		/// <summary>
		/// Close connection to database
		/// </summary>
		public void Close()
		{
			if (_connector.Connection != null && _connector.Connection.State == ConnectionState.Open)
			{
				_connector.Connection.Close();
				_connector.Connection = null;
			}
		}

		/// <summary>
		/// Load string connections from file
		/// </summary>
		/// Opens a file from the drive and looks for the database connection information
		private void LoadConnector()
		{
			int threadId = System.Threading.Thread.CurrentThread.GetHashCode();

			if (ApplicationHelper.DbConnThreads == null)
			{
				ApplicationHelper.DbConnThreads = new List<DbThreadHolder>();
			}
			DbThreadHolder dth = ApplicationHelper.DbConnThreads.Find(delegate(DbThreadHolder th)
			{
				return th.ThreadId == threadId; 
			});

			if (dth == null) // then create a new thread connection holder
			{
				dth = new DbThreadHolder();
				dth.ThreadId = threadId;
				dth.DbConnectors = ApplicationHelper.CfgGetConnections(DBPrefix); //GetConnectionStrings(); // same as load, but for a thread and returns the dictionary
				ApplicationHelper.DbConnThreads.Add(dth);
			}
			// if no default database given, then use one from the file.
			if (_database == "")
				_database = ApplicationHelper.DefaultDB;
			_connector = DataConnector(dth, _database); // dth.DbConnectors[m_database];
			return;
		}

		private DbConnector DataConnector(DbThreadHolder dth, string database)
		{
			if (!dth.DbConnectors.ContainsKey(database))
			{
				throw new Exception("Database Connection not found");
			}
			try
			{ 
				return dth.DbConnectors[database]; 
			}
			catch(Exception ex)
			{
				throw new Exception("Connection Error", ex);
				
			}
			
		}

		public static Dictionary<string, DbConnector> GetConnectionStrings()
		{
			return ApplicationHelper.CfgGetConnections(DBPrefix);
		}

		/// <summary>
		/// Create command
		/// </summary>
		/// <param name="ProcName">Name of procedure</param>
		/// <param name="ct">Type of command</param>
		/// <returns></returns>
		public SqlCommand CreateCommand(string ProcName,CommandType ct)
		{
			SqlCommand cmd=null;

			try
			{
				if (_connector.Transaction == null)
					cmd = new SqlCommand(ProcName, _connector.Connection);
				else
					cmd = new SqlCommand(ProcName, _connector.Connection, _connector.Transaction);
				cmd.CommandType=ct;
			}
			catch(Exception er)
			{
				throw new Exception("Connection Error",er);
			}
			return cmd;
		}

		public SqlDataAdapter CreateAdapter(string ProcName)
		{
			SqlDataAdapter adp = null;
			try
			{
				adp=new SqlDataAdapter(ProcName,_connector.Connection);
			}
			catch(Exception er)
			{
				throw new Exception("Connection Error", er);
			}
			return adp;

		}
	}
}
